package com.durablemethods.tsmserver.home;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.durablemethods.tsmserver.model.Position;
import com.durablemethods.tsmserver.model.TeamSummary;
import com.google.appengine.api.rdbms.AppEngineDriver;

public class PositionHome {

	public List<Position> getPositions() {
		List<Position> returnList = new ArrayList<Position>();
		Connection c = null;
	      try {
	    	//printWriter.println("Register Driver - pre");
			DriverManager.registerDriver(new AppEngineDriver());
			//printWriter.println("Register Driver -- done -- getting connections");
			c = DriverManager.getConnection("jdbc:google:rdbms://durablemethods.com:duravest:tsm","jamie","catal0g");
			//printWriter.println("We have a connection");
			Statement s = c.createStatement();
			//printWriter.println("Executing Query -- pre");
			ResultSet rs = s.executeQuery(getQuery());
			while (rs.next()){
				Position returnPosition = new Position();
				returnPosition.setTeam(rs.getString("Team"));
				returnPosition.setTrader(rs.getString("Trader"));
				returnPosition.setShares(rs.getInt("Shares"));
				returnList.add(returnPosition);
			}
		} catch (SQLException e) {
			//printWriter.println("Error found");
			//printWriter.println(e.getMessage());
			e.printStackTrace();
		}
		return returnList;
	}

	private String getQuery() {
		StringBuilder builder = new StringBuilder();
		builder.append("select Team, Trader, sum(Quantity) as Shares");
		builder.append(" from (");
		builder.append(" select ");
		builder.append(" teams.name as Team, traders.NAME as Trader, trans.quantity as Quantity");
		builder.append(" from TEM_SPORTS.TRADES trans, TEM_SPORTS.TEAMS teams, TEM_SPORTS.TRADERS traders");
		builder.append(" where trans.team_id = teams.id");
		builder.append(" and trans.trader_id = traders.id");
		builder.append(" and traders.id!=1");
		builder.append(" ) t");
		builder.append(" group by Team, Trader");
		builder.append(" order by Team, Trader");
		return builder.toString();
	}

}
